﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data;
using System.ComponentModel;

namespace Event_SG_EWDT
{
    public class DBQUERIES
    {
        #region GroupType + Group
        public static List<Group> GetGroupByGroupType(String group_type_id)
        {
            List<Group> list = new List<Group>();

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Group_Type gt INNER JOIN Group g ON gt.group_type_id=g.group_type_id WHERE group_type_id=@group_type_id";
                comm.Parameters.AddWithValue("@group_type_id", group_type_id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Group g = new Group();
                    //g.GroupTypeId = dr["group_type_id"].ToString();
                    //g.Description = dr[];
                    //g.GroupName;
                    //g.GroupPrivacy;
                    //g.GroupId=..GroupTypeName = (string)dr["group_type_name"];

                    list.Add(g);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return list;
        }
        #endregion



        public static ArrayList GetTopGroup()
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select group_type_id, group_id, group_name, description, mc from (select  t.group_type_id, g.group_id, g.group_name, g.description,g.group_privacy, COUNT(membership_no) mc from Group_Type t, Grouping g, Member_Group mc where t.group_type_id = g.group_type_id and g.group_id= mc.group_id group by t.group_type_id, g.group_privacy, g.group_id, g.group_name, g.description)t1, (select group_type_id gt, max(mc) mm from (select t.group_type_id, g.group_id, COUNT(membership_no) mc from Group_Type t, Grouping g, Member_Group mc where t.group_type_id = g.group_type_id and g.group_id= mc.group_id group by t.group_type_id, g.group_id)g2 group by group_type_id) t2 where t1.group_type_id = t2.gt and t1.mc = t2.mm and t1.group_privacy !=2  order by mc desc ";


                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[1].ToString();  //event_id
                    s[1] = dr[2].ToString();  //event_title
                    s[2] = dr[3].ToString();  //desc
                    s[3] = dr[0].ToString();  //max(cnt)
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }
        public static ArrayList GetTopSixGroup()
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select top 6 group_type_id, group_id, group_name, description, mc from (select  t.group_type_id, g.group_id, g.group_name, g.description,g.group_privacy, COUNT(membership_no) mc from Group_Type t, Grouping g, Member_Group mc where t.group_type_id = g.group_type_id and g.group_id= mc.group_id group by t.group_type_id, g.group_privacy, g.group_id, g.group_name, g.description)t1, (select group_type_id gt, max(mc) mm from (select t.group_type_id, g.group_id, COUNT(membership_no) mc from Group_Type t, Grouping g, Member_Group mc where t.group_type_id = g.group_type_id and g.group_id= mc.group_id group by t.group_type_id, g.group_id)g2 group by group_type_id) t2 where t1.group_type_id = t2.gt and t1.mc = t2.mm and t1.group_privacy !=2  order by mc desc ";


                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[1].ToString();  //event_id
                    s[1] = dr[2].ToString();  //event_title
                    s[2] = dr[3].ToString();  //desc
                    s[3] = dr[0].ToString();  //max(cnt)
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }

        public static ArrayList SearchGroupViaCategory(int group_type_id)
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from ( Select group_id, group_name, group_privacy, description from  grouping where  group_type_id = @group_type_id) t1, ( select group_id,count(*) c from member_group  group by group_id ) t2 where t1.group_id = t2.group_id and group_privacy ! =2   order by 6 desc  ";

                comm.Parameters.AddWithValue("@group_type_id", group_type_id);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[0].ToString();  //event_id
                    s[1] = dr[1].ToString();  //event_title
                    s[2] = dr[3].ToString();  //desc
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }
        public static DataTable RetrieveMemberGroupByMemberID(String memid)
        {
            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT      Grouping.group_name, Member_Group.group_id, member_group.member_type FROM Grouping INNER JOIN Member_Group ON Grouping.group_id = Member_Group.group_id where membership_no = @membership_no and Member_group.member_type = 'Admin' ";
                comm.Parameters.AddWithValue("@membership_no", Convert.ToInt32(memid));
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);

                //while (dr.Read())
                //{
                //    MemberGroup mg = new MemberGroup();
                //    mg.MembershipNo = memid;
                //    mg.GroupId = dr["group_id"].ToString();
                //    mg.MemberType = dr["member_type"].ToString();
                //    mg.Status = (int)dr["status"];

                //    list.Add(mg);
                //}
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }
        public static DataTable RetrieveMyReadNotification(String receiver)
        {

            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT notice_id, notification, sender, receiver, time, awareness FROM Notification where receiver = @receiver and awareness =1 order by 1 desc";
                comm.Parameters.AddWithValue("@receiver", Convert.ToInt32(receiver));
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);

                //while (dr.Read())
                //{
                //    Event ev = new Event();


                //    ev.EventId = dr[1].ToString();
                //    ev.EventTitle = dr[2].ToString();
                //    ev.Description = dr[3].ToString();
                //    tempAL.Add(ev);
                //}
                dr.Close();
                return tempDT;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);

            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }
        public static DataTable RetrieveMyUnReadNotification(String receiver)
        {

            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT notice_id, notification, sender, receiver, time, awareness FROM Notification where receiver = @receiver and awareness =0 order by 1 desc";
                comm.Parameters.AddWithValue("@receiver", Convert.ToInt32(receiver));
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);

                //while (dr.Read())
                //{
                //    Event ev = new Event();


                //    ev.EventId = dr[1].ToString();
                //    ev.EventTitle = dr[2].ToString();
                //    ev.Description = dr[3].ToString();
                //    tempAL.Add(ev);
                //}
                dr.Close();
                return tempDT;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);

            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }


        public static ArrayList SearchEventViaCategory(int category)
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from ( select event_id, event_title, privacy, description from event where category_id = @category ) t1, (select event_id, count(*) c from member_event group by event_id) t2 where t1.event_id = t2.event_id and privacy !=2 order by 6 desc  ";

                comm.Parameters.AddWithValue("@category", category);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[0].ToString();  //event_id
                    s[1] = dr[1].ToString();  //event_title
                    s[2] = dr[3].ToString();  //desc
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }
        public static ArrayList GetTopEventFromEachType()
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select category_id, event_id, event_title, membership_count, description from (select c.category_id,e.event_title, e.event_id, e.description, count(m.membership_no) membership_count from Member_Event m, Event e, Category c  where m.event_id = e.event_id and e.category_id = c.category_id and privacy!=2 and (CURRENT_TIMESTAMP between start_time and end_time or start_time > CURRENT_TIMESTAMP) group by c.category_id, e.event_id, e.event_title, e.description ) t1, ( select category_id cc, max(membership_count) mm from  (select c.category_id, e.event_id, count(m.membership_no) membership_count from Member_Event m, Event e, Category c where m.event_id = e.event_id and e.category_id = c.category_id group by c.category_id, e.event_id ) count_table2  group by category_id ) t2 where t1.category_id = t2.cc and t1.membership_count = t2.mm";


                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[3];
                    s[0] = dr[1].ToString();  //category id
                    s[1] = dr[2].ToString();  //event id
                    s[2] = dr[4].ToString();  //memberShip_count
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;
        }
        public static ArrayList GetTenHighLight()
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select top 6 event_id, event_title,description, max(cnt)mm from" +
                    " (select e.event_id, e.event_title, e.description, Count(membership_no) cnt" +
    " from Member_Event m, event e " +
    " where m.event_id = e.event_id and privacy!=2 and (CURRENT_TIMESTAMP between start_time and end_time or start_time > CURRENT_TIMESTAMP)" +
    " group by e.event_id, event_title, description) c1" +
    " group by event_id, event_title, description" +
    " order by mm desc";


                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[0].ToString();  //event_id
                    s[1] = dr[1].ToString();  //event_title
                    s[2] = dr[2].ToString();  //desc
                    s[3] = dr[3].ToString();  //max(cnt)
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;
        }
        public static ArrayList SearchEvent(string EventTitle)
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from (select * from event where event_title like '%'+ @Eventtitle + '%') t1, (select event_id, count( *) c from member_event group by event_id)t2 where t1. event_id = t2.event_id and privacy !=2 order by c desc";

                comm.Parameters.AddWithValue("@Eventtitle", EventTitle);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[0].ToString();  //event_id
                    s[1] = dr[1].ToString();  //event_title
                    s[2] = dr[2].ToString();  //desc
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }
        public static ArrayList SearchGroup(string GroupName)
        {
            SqlConnection conn = null;
            ArrayList ar = new ArrayList();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from ( select * from grouping where group_name like '%'+ @Groupname + '%') t1, (select group_id, count(*) c from member_group group by group_id) t2  where t1.group_id = t2.group_id   and group_privacy !=2   order by c desc ";

                comm.Parameters.AddWithValue("@Groupname", GroupName);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string[] s = new string[4];
                    s[0] = dr[0].ToString();  //event_id
                    s[1] = dr[1].ToString();  //event_title
                    s[2] = dr[2].ToString();  //desc
                    s[3] = dr[3].ToString();  //max(cnt)
                    ar.Add(s);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }

     
        public static DataTable RetrieveGroupPendingList(int group_id)
        {

            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Grouping.group_id, Grouping.group_name, Member.membership_no, Member.name FROM         Grouping INNER JOIN                       Member_Group ON Grouping.group_id = Member_Group.group_id INNER JOIN                       Member ON Member_Group.membership_no = Member.membership_no                       where member_group.status = 3   and   Grouping.group_id =@group_id ";
                comm.Parameters.AddWithValue("@group_id", group_id);
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;

        }



        #region Event

        public static List<Group> Test(String group_type_id)
        {
            List<Group> list = new List<Group>();

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "";
                comm.Parameters.AddWithValue("@group_type_id", group_type_id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Group g = new Group();
                    //g.GroupTypeId = dr["group_type_id"].ToString();
                    //g.Description = dr[];
                    //g.GroupName;
                    //g.GroupPrivacy;
                    //g.GroupId=..GroupTypeName = (string)dr["group_type_name"];

                    list.Add(g);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return list;
        }

        public static DataTable RetrieveEventPendingList(int event_id)
        {

            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.membership_no, Member.name, Event.event_id, Event.event_title FROM  Member INNER JOIN    Member_Event ON Member.membership_no = Member_Event.membership_no INNER JOIN  Event ON Member_Event.event_id = Event.event_id                       where status = 4   and Event.event_id =@event_id";
                comm.Parameters.AddWithValue("@event_id", event_id);
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);
                dr.Close();
                //while (dr.Read())
                //{
                //    MemberGroup mg = new MemberGroup();
                //    mg.MembershipNo = memid;
                //    mg.GroupId = dr["group_id"].ToString();
                //    mg.MemberType = dr["member_type"].ToString();
                //    mg.Status = (int)dr["status"];

                //    list.Add(mg);
                //}

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;

        }

        #endregion


        # region odsMyEvent

        public static DataTable MyUpcomingEvent(String memberShip_no) //ok //hash password later
        {
            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Member_event.member_role, Member_Event.membership_no, Member_Event.event_id, Event.event_title, Event.description, Event.start_time, Event.end_time FROM Member_Event CROSS JOIN Event where member_event.event_id = event.event_id and membership_no=@membership_no and ( CURRENT_TIMESTAMP between start_time and end_time or start_time > CURRENT_TIMESTAMP )";
                comm.Parameters.AddWithValue("@membership_no", memberShip_no);
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);

                //while (dr.Read())
                //{
                //    Event ev = new Event();


                //    ev.EventId = dr[1].ToString();
                //    ev.EventTitle = dr[2].ToString();
                //    ev.Description = dr[3].ToString();
                //    tempAL.Add(ev);
                //}
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }
        public static DataTable MyPastEvent(String memberShip_no) //ok //hash password later
        {
            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Member_event.member_role, Member_Event.membership_no, Member_Event.event_id, Event.event_title, Event.description, Event.start_time, Event.end_time FROM Member_Event CROSS JOIN Event where member_event.event_id = event.event_id and membership_no=@membership_no and not ( CURRENT_TIMESTAMP between start_time and end_time or start_time > CURRENT_TIMESTAMP )";
                comm.Parameters.AddWithValue("@membership_no", memberShip_no);
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);

                //while (dr.Read())
                //{
                //    Event ev = new Event();


                //    ev.EventId = dr[1].ToString();
                //    ev.EventTitle = dr[2].ToString();
                //    ev.Description = dr[3].ToString();
                //    tempAL.Add(ev);
                //}
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }
        public static DataTable MyGroup(String membership_no) //ok //hash password later
        {
            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member_Group.group_id, Grouping.group_name, Member_Group.member_type ,t1.mc FROM        (select group_id,count(*) mc from member_group where status=1 group by group_id)t1, Member_Group  INNER JOIN           Grouping ON Member_Group.group_id = Grouping.group_id INNER JOIN Member ON Member_Group.membership_no = Member.membership_no WHERE     (Member_Group.membership_no =@membership_no) and Member_Group.group_id = t1.group_id";
                comm.Parameters.AddWithValue("@membership_no", membership_no);
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);

                //while (dr.Read())
                //{
                //    Event ev = new Event();


                //    ev.EventId = dr[1].ToString();
                //    ev.EventTitle = dr[2].ToString();
                //    ev.Description = dr[3].ToString();
                //    tempAL.Add(ev);
                //}
                dr.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }
        #endregion


        public static List<Member> RetrieveWhoIsNotInTheEventFromGroup(string groupid, string eventid)
        {
            SqlConnection conn = null;
            List<Member> ar = new List<Member>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from ( Select *  from member where membership_no  in (SELECT     Member_Group.membership_no FROM         Member_Group INNER JOIN  Member ON Member_Group.membership_no = Member.membership_no where group_id = @groupid)) t1, (select * from member where membership_no not in ( SELECT     Member.membership_no FROM         Member INNER JOIN    Member_Event ON Member.membership_no = Member_Event.membership_no INNER JOIN                     Member_Group ON Member.membership_no = Member_Group.membership_no where event_id = @eventid )) t2 where t1.membership_no = t2.membership_no";

                comm.Parameters.AddWithValue("@groupid", groupid);
                comm.Parameters.AddWithValue("@eventid", eventid);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Member m = new Member();
                    m.MembershipNo = dr[0].ToString();
                    m.Name = dr[1].ToString();
                    m.Handphone = dr[2].ToString();
                    m.Email = dr[3].ToString();
                    ar.Add(m);
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return ar;

        }
        public static int UpdateNotificationTORead(string receiver)
        {
            SqlConnection conn = new SqlConnection();
            List<Notification> a = new List<Notification>();
                int i =0;
                try
                {
                    conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "update Notification set awareness = @awareness where receiver = @receiver";
                    comm.Parameters.AddWithValue("@awareness", 1);
                    comm.Parameters.AddWithValue("@receiver", receiver);

                    i = comm.ExecuteNonQuery();


                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);

                }
                finally
                {

                    conn.Close();
                }
            return i;
        }

        public static List<Notification> NoOfUnreadNotification(string receiver)
        {
            SqlConnection conn = new SqlConnection();
            List<Notification> a = new List<Notification>();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     notice_id , notification , sender , receiver , time , awareness FROM         Notification where receiver = @receiver and awareness = 0";
                comm.Parameters.AddWithValue("@receiver", receiver);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Notification n = new Notification();

                    n.Notice_id = dr[0].ToString();
                    n.NotificationContent = dr[1].ToString();
                    n.sender = dr[2].ToString();
                    n.receiver = dr[3].ToString();

                    a.Add(n);
                }
                dr.Close();
                return a;

                //while (dr.Read())
                //{
                //    Event ev = new Event();


                //    ev.EventId = dr[1].ToString();
                //    ev.EventTitle = dr[2].ToString();
                //    ev.Description = dr[3].ToString();
                //    tempAL.Add(ev);
                //}
                //dr.Close();
                //return a;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return a;
        }
        public static DataTable RetrieveParticipants_Event(string eventid)
        {

            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.* ,  member_event.member_role, status  FROM         Member INNER JOIN  Member_Event ON Member.membership_no = Member_Event.membership_no where status = 0 and  member_event.event_id = @eventid and member_role!='Admin'";
                comm.Parameters.AddWithValue("@eventid", Convert.ToInt32(eventid));
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);
                dr.Close();
                //while (dr.Read())
                //{
                //    MemberGroup mg = new MemberGroup();
                //    mg.MembershipNo = memid;
                //    mg.GroupId = dr["group_id"].ToString();
                //    mg.MemberType = dr["member_type"].ToString();
                //    mg.Status = (int)dr["status"];

                //    list.Add(mg);
                //}
                
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;

        }
        
        [DataObjectMethod(DataObjectMethodType.Update,true)]
        public static void UpdateMemberEvent_Role(MemberEvent me)
        {
            DBMANAGER.UpdateMemberEvent(me);

        }
        
        
        
        
        
        public static bool DeleteMemberGroup(MemberGroup mg)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Member_Group WHERE membership_no=@membership_no and group_id=@group_id";

                comm.Parameters.AddWithValue("@membership_no", mg.MembershipNo);
                comm.Parameters.AddWithValue("@group_id", mg.GroupId);
                int rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected == 1)
                {
                    return true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static void UpdateMemberGroup_Role(string event_id, string member_id, string member_role, int status)
        {
            
            EventControl.UpdateMemberEvent_Role(event_id, member_id, member_role); 

        }

        public static DataTable RetrieveGroupMembers(string groupid)
        {
            SqlConnection conn = new SqlConnection();
            DataTable tempDT = new DataTable();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EventSGDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT     Member.* ,  member_group.member_type, status  FROM  Member INNER JOIN  Member_Group ON Member.membership_no = Member_Group.membership_no where status = 1 and  member_group.group_id = @groupid and member_type!='Admin'";
                comm.Parameters.AddWithValue("@groupid", Convert.ToInt32(groupid));
                SqlDataReader dr = comm.ExecuteReader();

                tempDT.Load(dr);
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return tempDT;
        }
    }


}
